USE CAFEDB
GO

CREATE PROC GETNUMBEROFEXPORT(@TABLEID INT,@EMPLOYERID INT)
AS
DECLARE @EXP VARCHAR(10)
DECLARE @EXP1 VARCHAR(10)='HD'
DECLARE @EXP2 VARCHAR(10)
	BEGIN
		SELECT @EXP=ISNULL(RIGHT('00' + CAST( (CAST((SELECT TOP 1 SUBSTRING(EXPORTID,3,8)FROM EXPORT ORDER BY DATECREATE DESC ) AS INT)+1) AS varchar), 2),'00')
		IF(@EXP='00')
		BEGIN
			SET @EXP='01'
		END
		SET @EXP2=@EXP1+@EXP
		INSERT INTO EXPORT([EXPORTID],[TABLEID],[EMPLOYERID],[TABLEDISCOUNT],[DATECREATE],[ISPAYALL],[VAT],[SERVICE])
		VALUES(@EXP2,@TABLEID,@EMPLOYERID,0,GETDATE(),0,0,0)

		SELECT @EXP2
	END
	
GO

CREATE PROC GETDETAILEXPORTBYTABLEID(@TABLEID INT)
AS
	BEGIN
		SELECT EXPORT.EXPORTID,EXPORTDETAIL.ITEMID,QUANTITY,EXPORTDETAIL.PRICE,ITEMDISCOUNT,ISITEMPAY,ITEMNAME,TOTAL=((QUANTITY*EXPORTDETAIL.PRICE)-(QUANTITY*EXPORTDETAIL.PRICE*ITEMDISCOUNT)/100),VAT,[SERVICE],TABLEDISCOUNT
		FROM EXPORT,EXPORTDETAIL,ITEM
		WHERE EXPORT.EXPORTID=EXPORTDETAIL.EXPORTID 
			AND ITEM.ITEMID=EXPORTDETAIL.ITEMID
			AND TABLEID=@TABLEID
			AND EXPORT.ISPAYALL=0
	END
GO

CREATE PROC GETDETAILEXPORTBYEXPORTID(@EXPORTID VARCHAR(10))
AS
	BEGIN
		SELECT EXPORT.EXPORTID,EXPORTDETAIL.ITEMID,QUANTITY,EXPORTDETAIL.PRICE,ITEMDISCOUNT,ISITEMPAY,ITEMNAME,TOTAL=((QUANTITY*EXPORTDETAIL.PRICE)-(QUANTITY*EXPORTDETAIL.PRICE*ITEMDISCOUNT)/100),VAT,[SERVICE],TABLEDISCOUNT
		FROM EXPORT,EXPORTDETAIL,ITEM
		WHERE EXPORT.EXPORTID=EXPORTDETAIL.EXPORTID 
			AND ITEM.ITEMID=EXPORTDETAIL.ITEMID
			AND EXPORT.EXPORTID=@EXPORTID

	END
GO

Create PROC ISTABLEHAVEGUEST(@TABLEID INT)
AS
 declare  @result int
	BEGIN
		 SELECT @result=TABLEID
		FROM EXPORT
		WHERE TABLEID=@TABLEID AND ISPAYALL=0

		if(@result>0)
		SELECT @result
		else
		SELECT 0;
		
	END

	GO

CREATE PROC UPDATEVATSERVICEDISCOUNT(@EXPORTID VARCHAR(10),@VAT FLOAT,@SERVICE FLOAT,@DISCOUNT FLOAT)
AS
	BEGIN
		UPDATE EXPORT SET VAT = @VAT,[SERVICE]=@SERVICE,TABLEDISCOUNT=@DISCOUNT 
		WHERE EXPORTID=@EXPORTID
	END

GO

CREATE PROC ADDITEMTOTABLE(@EXPORTID VARCHAR(10),@ITEMID INT,@QUANTITY INT)
AS
DECLARE @PRICE FLOAT
DECLARE @ISITEMIDEXIST VARCHAR
DECLARE @ISDELETE BIT
DECLARE @ITEMCHECK FLOAT
	BEGIN
	SET  @PRICE= (SELECT PRICE FROM ITEM WHERE ITEMID=@ITEMID)
	SET @ISITEMIDEXIST= (SELECT COUNT(ITEMID) FROM EXPORTDETAIL WHERE EXPORTID=@EXPORTID AND ITEMID=@ITEMID)
	IF(@ISITEMIDEXIST>0)
		UPDATE EXPORTDETAIL SET QUANTITY=(QUANTITY+@QUANTITY) WHERE EXPORTID=@EXPORTID AND ITEMID=@ITEMID
	ELSE
		INSERT INTO EXPORTDETAIL(EXPORTID,ITEMID,QUANTITY,PRICE,ITEMDISCOUNT,ISITEMPAY)
			VALUES(@EXPORTID,@ITEMID,@QUANTITY,@PRICE,0,0)
	
	SET @ITEMCHECK=(SELECT QUANTITY FROM EXPORTDETAIL WHERE  EXPORTID=@EXPORTID AND ITEMID=@ITEMID)
	IF (@ITEMCHECK=NULL)
	BEGIN
		SET @ITEMCHECK=0
		END
	IF(@ITEMCHECK<=0)
		BEGIN
			 DELETE FROM EXPORTDETAIL WHERE  EXPORTID=@EXPORTID AND ITEMID=@ITEMID
		 END
	 IF((SELECT COUNT(ITEMID) FROM EXPORTDETAIL WHERE  EXPORTID=@EXPORTID)=0)
		 BEGIN
			 DELETE FROM EXPORTDETAIL WHERE EXPORTID=@EXPORTID
			 DELETE FROM EXPORT  WHERE EXPORTID=@EXPORTID
			 SET @ISDELETE=1
		 END
	 ELSE 
		SET @ISDELETE=0

	SELECT @ISDELETE
	END

	GO

	CREATE PROC UPDATEDISCOUNTFORITEM(@EXPORTID VARCHAR(10),@ITEMID INT, @ITEMDISCOUNT FLOAT)
	AS
		BEGIN
			UPDATE EXPORTDETAIL SET ITEMDISCOUNT=@ITEMDISCOUNT
			WHERE EXPORTID=@EXPORTID AND ITEMID=@ITEMID
		END
	
	GO

	CREATE PROC PAIDBILL(@EXPORTID VARCHAR(10), @DATEPAID DATETIME)
	AS

		BEGIN
			UPDATE EXPORT
			 SET ISPAYALL=1,DATEPAID = @DATEPAID
			 WHERE EXPORT.EXPORTID=@EXPORTID

		END
		
		go
	CREATE PROC GETEXPORTBYEXPORTID(@EXPORTID VARCHAR(10))
	AS

		BEGIN
			SELECT EXPORT.EXPORTID, EXPORT.TABLEID,[TABLE].TABLENAME,
			EXPORT.EMPLOYERID,EMPLOYER.FULLNAME, TABLEDISCOUNT,
			EXPORT.DATECREATE,ISPAYALL,VAT,[SERVICE], DATEPAID , AREANAME
			FROM EXPORT,[TABLE], EMPLOYER, AREA
			 WHERE EXPORT.TABLEID=[TABLE].TABLEID
			 AND EXPORT.EMPLOYERID=EMPLOYER.EMPLOYERID
			 AND [TABLE].AREAID=AREA.AREAID
			 AND
			 EXPORT.EXPORTID=@EXPORTID

		END  

GO

CREATE PROC GETTABLEHAVEGUESTBYAREAID(@AREAID VARCHAR(20))
AS
	BEGIN
		SELECT [TABLE].TABLEID, TABLENAME ,AREA.AREAID
		FROM [TABLE],EXPORT,AREA
		WHERE [TABLE].TABLEID = EXPORT.TABLEID
		AND AREA.AREAID = [TABLE].AREAID
		AND [TABLE].AREAID=@AREAID
		AND  ISPAYALL = 0
	END
GO

CREATE PROC GETTABLENOTHAVEGUESTBYAREAID(@AREAID VARCHAR(20))
AS
	BEGIN
		SELECT [TABLE].TABLEID, TABLENAME ,AREA.AREAID
		FROM [TABLE],AREA
		WHERE [TABLE].TABLEID=[TABLE].TABLEID
		AND AREA.AREAID = [TABLE].AREAID
		AND AREA.AREAID =@AREAID
		AND [TABLE].[TABLEID] NOT IN (SELECT TABLEID  FROM EXPORT WHERE ISPAYALL = 0)
	END
GO


CREATE PROC BAOCAOTONGHOP(@FROM DATE, @TO DATE)
AS

	BEGIN
	
		SELECT TATCABAN=(SELECT COUNT(EXPORTID)  FROM EXPORT WHERE DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO),

 TONGTIENTATCA=(
SELECT SUM(((QUANTITY * PRICE) + (QUANTITY * PRICE)*VAT/100 + (QUANTITY * PRICE)*[SERVICE]/100)
			- ((QUANTITY * PRICE)- (  ((QUANTITY * PRICE)*ITEMDISCOUNT)/100)  )*TABLEDISCOUNT/100 -  (((QUANTITY * PRICE)*ITEMDISCOUNT)/100))
					 FROM EXPORTDETAIL,EXPORT WHERE EXPORT.EXPORTID=EXPORTDETAIL.EXPORTID AND DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO) ,

 SOBANDATHU=(SELECT COUNT(EXPORTID) FROM EXPORT WHERE ISPAYALL=1 AND DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO),

 TONGTIENDATHU=(
SELECT SUM(((QUANTITY * PRICE) + (QUANTITY * PRICE)*VAT/100 + (QUANTITY * PRICE)*[SERVICE]/100)
			- ((QUANTITY * PRICE)- (  ((QUANTITY * PRICE)*ITEMDISCOUNT)/100)  )*TABLEDISCOUNT/100 -  (((QUANTITY * PRICE)*ITEMDISCOUNT)/100))

					 FROM EXPORTDETAIL,EXPORT WHERE EXPORT.EXPORTID=EXPORTDETAIL.EXPORTID AND DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO AND ISPAYALL =1),

 SOBANCHUATHU=(SELECT COUNT(EXPORTID) FROM EXPORT WHERE ISPAYALL=0 AND DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO),

 TONGTIENCHUATHU=SUM(((QUANTITY * PRICE) + (QUANTITY * PRICE)*VAT/100 + (QUANTITY * PRICE)*[SERVICE]/100)

			- ((QUANTITY * PRICE)- (  ((QUANTITY * PRICE)*ITEMDISCOUNT)/100)  )*TABLEDISCOUNT/100 -  (((QUANTITY * PRICE)*ITEMDISCOUNT)/100))

					 FROM EXPORTDETAIL,EXPORT WHERE EXPORT.EXPORTID=EXPORTDETAIL.EXPORTID AND DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO AND ISPAYALL=0
	END
GO

CREATE PROC BAOCAODOANHTHUTHEOHOADON(@FROM DATE, @TO DATE)
AS
	BEGIN
		SELECT 
				EXPORT.EXPORTID, AREA.AREANAME, [TABLE].TABLENAME,EXPORT.DATECREATE, TOTAL=SUM(QUANTITY*PRICE),
				[SERVICE]=(SUM(QUANTITY*PRICE)*[SERVICE])/100,
				[VAT]=(SUM(QUANTITY*PRICE)*[VAT])/100,
				ITEMDISCOUNT=SUM(QUANTITY*PRICE*ITEMDISCOUNT/100*-1),
				TABLEDISCOUNT= (SUM((QUANTITY*PRICE) - (QUANTITY*PRICE*ITEMDISCOUNT/100))*TABLEDISCOUNT)/100*-1,
				FINALTOTAL= (SUM(QUANTITY*PRICE)+(SUM(QUANTITY*PRICE)*[SERVICE])/100 +(SUM(QUANTITY*PRICE)*[VAT])/100)
					-((SUM(QUANTITY*PRICE*ITEMDISCOUNT/100))+((SUM((QUANTITY*PRICE) - (QUANTITY*PRICE*ITEMDISCOUNT/100))*TABLEDISCOUNT)/100))

		FROM EXPORT,EXPORTDETAIL,AREA,[TABLE]
		WHERE EXPORT.EXPORTID = EXPORTDETAIL.EXPORTID
				AND EXPORT.TABLEID= [TABLE].TABLEID
				AND [TABLE].AREAID = AREA.AREAID
				AND   DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO
		GROUP BY EXPORT.EXPORTID, AREA.AREANAME, [TABLE].TABLENAME,EXPORT.DATECREATE, EXPORT.[SERVICE],EXPORT.VAT,EXPORT.TABLEDISCOUNT,EXPORT.VAT
		ORDER BY TOTAL DESC
	END
GO

CREATE PROC GETLISTEXPORT(@FROM DATE, @TO DATE, @ISPAYALL BIT)
AS
	BEGIN
		SELECT 
				EXPORT.EXPORTID, AREA.AREANAME, [TABLE].TABLENAME,EXPORT.DATECREATE, TOTAL=SUM(QUANTITY*PRICE),
				[SERVICE]=(SUM(QUANTITY*PRICE)*[SERVICE])/100,
				[VAT]=(SUM(QUANTITY*PRICE)*[VAT])/100,
				ITEMDISCOUNT=SUM(QUANTITY*PRICE*ITEMDISCOUNT/100*-1),
				TABLEDISCOUNT= (SUM((QUANTITY*PRICE) - (QUANTITY*PRICE*ITEMDISCOUNT/100))*TABLEDISCOUNT)/100*-1,
				FINALTOTAL= (SUM(QUANTITY*PRICE)+(SUM(QUANTITY*PRICE)*[SERVICE])/100 +(SUM(QUANTITY*PRICE)*[VAT])/100)
					-((SUM(QUANTITY*PRICE*ITEMDISCOUNT/100))+((SUM((QUANTITY*PRICE) - (QUANTITY*PRICE*ITEMDISCOUNT/100))*TABLEDISCOUNT)/100)),
				EXPORT.TABLEID

		FROM EXPORT,EXPORTDETAIL,AREA,[TABLE]
		WHERE EXPORT.EXPORTID = EXPORTDETAIL.EXPORTID
				AND EXPORT.TABLEID= [TABLE].TABLEID
				AND [TABLE].AREAID = AREA.AREAID
				AND   DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO
				AND ISPAYALL=@ISPAYALL
		GROUP BY EXPORT.EXPORTID, AREA.AREANAME, [TABLE].TABLENAME,EXPORT.DATECREATE, EXPORT.[SERVICE],EXPORT.VAT,EXPORT.TABLEDISCOUNT,EXPORT.VAT,EXPORT.TABLEID
		ORDER BY TOTAL DESC
	END
GO


CREATE PROC BAOCAODOANHTHUTHEOMONAN(@FROM DATE, @TO DATE)
AS
	BEGIN
		SELECT ITEM.ITEMNAME,SL=SUM(QUANTITY),
		TOTAL=SUM(((QUANTITY * EXPORTDETAIL.PRICE) + (QUANTITY * EXPORTDETAIL.PRICE)*VAT/100 + (QUANTITY * EXPORTDETAIL.PRICE)*[SERVICE]/100)

			- ((QUANTITY * EXPORTDETAIL.PRICE)- (  ((QUANTITY * EXPORTDETAIL.PRICE)*ITEMDISCOUNT)/100)  )*TABLEDISCOUNT/100 -  (((QUANTITY * EXPORTDETAIL.PRICE)*ITEMDISCOUNT)/100))
			
FROM ITEM,EXPORTDETAIL, EXPORT
WHERE ITEM.ITEMID = EXPORTDETAIL.ITEMID
AND EXPORT.EXPORTID = EXPORTDETAIL.EXPORTID
AND DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO
GROUP BY ITEMNAME
ORDER BY TOTAL DESC

	END
GO

CREATE PROC BAOCAODOANHTHUTHEONGAY(@FROM DATE, @TO DATE)
AS
	BEGIN
			SELECT NGAY=CAST( EXPORT.DATECREATE AS DATE),SL=SUM(QUANTITY),
		TOTAL=SUM(((QUANTITY * EXPORTDETAIL.PRICE) + (QUANTITY * EXPORTDETAIL.PRICE)*VAT/100 + (QUANTITY * EXPORTDETAIL.PRICE)*[SERVICE]/100)

			- ((QUANTITY * EXPORTDETAIL.PRICE)- (  ((QUANTITY * EXPORTDETAIL.PRICE)*ITEMDISCOUNT)/100)  )*TABLEDISCOUNT/100 -  (((QUANTITY * EXPORTDETAIL.PRICE)*ITEMDISCOUNT)/100))
			
FROM ITEM,EXPORTDETAIL, EXPORT
WHERE ITEM.ITEMID = EXPORTDETAIL.ITEMID
AND EXPORT.EXPORTID = EXPORTDETAIL.EXPORTID
AND DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO
GROUP BY CAST( EXPORT.DATECREATE AS DATE)
ORDER BY TOTAL DESC

	END
GO


CREATE PROC LOADROLEBYGROUPUSERCODE(@GROUPUSERCODE INT)
AS
	BEGIN
			SELECT  [ROLE].ROLENAME,PERMIT,
  GROUPROLE.GROUPROLENAME, [ROLE].[ROLECODE],DETAIL_GROUPUSER_ROLE.GROUPUSERCODE

FROM [ROLE],DETAIL_GROUPUSER_ROLE,GROUPROLE
WHERE [ROLE].ROLECODE =  DETAIL_GROUPUSER_ROLE.ROLECODE
AND [ROLE].GROUPROLECODE = GROUPROLE.GROUPROLECODE
AND DETAIL_GROUPUSER_ROLE.GROUPUSERCODE=@GROUPUSERCODE
	END
GO


CREATE PROC DELETEDETAILROLEBYGROUPUSERID(@GROUPUSERCODE INT)
AS
	BEGIN
			DELETE FROM DETAIL_GROUPUSER_ROLE
			WHERE DETAIL_GROUPUSER_ROLE.GROUPUSERCODE = @GROUPUSERCODE
	END
GO


CREATE PROC CHANGEPASSWORD(@USERNAME VARCHAR(30),@PASSWORDOLD VARCHAR(32), @PASSWORDNEW VARCHAR(32))
AS
	BEGIN
			UPDATE USERACCOUNT
			SET USERACCOUNT.PASSWORD=@PASSWORDNEW
			WHERE USERACCOUNT.USERNAME=@USERNAME
				AND USERACCOUNT.PASSWORD=@PASSWORDOLD
	END
GO
CREATE PROC RESETPASSWORD(@USERNAME VARCHAR(30), @PASSWORDNEW VARCHAR(32))
AS
	BEGIN
			UPDATE USERACCOUNT
			SET USERACCOUNT.PASSWORD=@PASSWORDNEW
			WHERE USERACCOUNT.USERNAME=@USERNAME
	END
GO


CREATE PROC CHECKROLE(@GROUPUSERCODE INT, @ROLECODE VARCHAR(50))
AS
	BEGIN
			SELECT PERMIT 
			FROM DETAIL_GROUPUSER_ROLE 
			WHERE GROUPUSERCODE=@GROUPUSERCODE
					AND ROLECODE=@ROLECODE
	END
GO

